package com.mf.framework.component.excel;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;

/**
 * Excel导出基础类
 * User: zhaoming
 * Date: 2017-03-16
 * To change this template use File | Settings | File Templates.
 **/
public class ExcelExportBase {

    private CellStyle integerCellStyle;
    private CellStyle floatCellStyle;
    private CellStyle stringCellStyle;
    private CellStyle dateCellStyle;
    /**
     * 获取标题单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getTitleStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);      //指定背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);             //指定填充方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);                     //指定单元格居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);               //指定单元格垂直居中对齐
        cellStyle.setFont(this.getFont(wb, "宋体", (short) 300, HSSFColor.BLACK.index, true));   //设置单元格字体
        return cellStyle;
    }

    /**
     * 获取副标题单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getSubTitleStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);                       //指定单元格居左对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);               //指定单元格垂直居中对齐
        cellStyle.setWrapText(true);                                            //指定单元格自动换行
        cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.RED.index, true));   //设置单元格字体
        return cellStyle;
    }

    /**
     * 获取列表头风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getHeaderStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);      //指定背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);             //指定填充方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);                     //指定单元格居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);               //指定单元格垂直居中对齐
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.BLACK.index, true));   //设置单元格字体
        return cellStyle;
    }

    /**
     * 获取整形单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getIntegerCellStyle(Workbook wb){
        if(this.integerCellStyle != null){
            return this.integerCellStyle;
        }else{
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            cellStyle.setDataFormat(wb.createDataFormat().getFormat("#0"));
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.BLACK.index, false));
            this.integerCellStyle = cellStyle;
        }
        return integerCellStyle;
    }

    /**
     * 获取浮点型单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getFloatCellStyle(Workbook wb){
        if(this.floatCellStyle != null){
            return this.floatCellStyle;
        }else{
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            cellStyle.setDataFormat(wb.createDataFormat().getFormat("#0.00"));
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.BLACK.index, false));
            this.floatCellStyle = cellStyle;
        }
        return floatCellStyle;
    }

    /**
     * 获取字符型单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getStringCellStyle(Workbook wb){
        if(this.stringCellStyle != null){
            return this.stringCellStyle;
        }else{
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.BLACK.index, false));
            this.stringCellStyle = cellStyle;
        }
        return stringCellStyle;
    }

    /**
     * 获取日期型单元格风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getDateCellStyle(Workbook wb){
        if(this.dateCellStyle != null){
            return this.dateCellStyle;
        }else{
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            cellStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.BLACK.index, false));
            this.dateCellStyle = cellStyle;
        }
        return dateCellStyle;
    }

    /**
     * 获取尾行风格
     * @param wb 工作薄
     * @return 单元格风格
     */
    protected CellStyle getFooterStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);                       //指定单元格居左对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);               //指定单元格垂直居中对齐
        cellStyle.setWrapText(true);                                            //指定单元格自动换行
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFont(this.getFont(wb, "宋体", (short) 200, HSSFColor.RED.index, true));   //设置单元格字体
        return cellStyle;
    }

    /**
     * 设置合并后的边框
     * @param border 边框
     * @param cellRangeAddress 合并范围
     * @param sheet 工作表
     */
    public void setBorder(int border, CellRangeAddress cellRangeAddress, Sheet sheet){
        RegionUtil.setBorderTop(border, cellRangeAddress, sheet);       //上边框
        RegionUtil.setBorderRight(border, cellRangeAddress, sheet);     //右边框
        RegionUtil.setBorderLeft(border, cellRangeAddress, sheet);      //左边框
        RegionUtil.setBorderBottom(border, cellRangeAddress, sheet);    //下边框
    }

    /**
     * 获取字体
     * @param wb 工作薄
     * @param fontName 字体名称
     * @param fontHeight 字体高度
     * @param fontColor 字体颜色
     * @param bold 是否加粗
     * @return
     */
    private Font getFont(Workbook wb, String fontName, short fontHeight, short fontColor, boolean bold){
        Font font = wb.createFont();
        font.setFontName(fontName); //字体名称
        font.setFontHeight(fontHeight); //字体高度
        font.setColor(fontColor);
        font.setBold(bold); //是否加粗
        return font;
    }

    /**
     * 导出-设置参数
     */
    public static void setResponseParam(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/msexcel;charset=GBK");
            response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("gbk"),"iso-8859-1"));
            response.setHeader("Connection", "close");
            response.setHeader("Content-Type", "application/vnd.ms-excel");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }
}
